/*
Table 1: Some statistics describing records in the inpatient and Emergency Department Sample
*/

libname inter "D:\Data\inter";

data for_tables_ed;
  set inter.rep_alcohol (where=( months_21 >= -24 and months_21 <= 23 and state in ('AZ','NJ','WI')) 
                      keep = inpatient months_21 female died medicaid private self_pay other_ins  preg_related TOTCHG place
                             inj_by_oth inj_by_self inj_accident alcohol_on_dx alcohol illness state alcohol_any);
   if place = "E8490" then	Home = 1; else Home = 0;
   if place = "E8493" then	Industrial_place = 1; else Industrial_place = 0;
   if place = "E8494" then	Place_recreation_sport = 1; else Place_recreation_sport = 0;
   if place = "E8495" then	Street_highway = 1; else Street_highway = 0;
   if place = "E8496" then	Public_building = 1; else Public_building = 0;
   if place = "E8497" then	Residential_institution = 1; else Residential_institution = 0;
   if place in ("E8498","E8499","E8491","E8492") then Other_place  = 1; else Other_place = 0;
   if place = "" then place_not_coded = 1; else place_not_coded = 0;
   injury_any = inj_accident  + inj_by_oth + inj_by_self;
run;
proc freq data = for_tables_ed;
   tables injury_any;
run;

data for_tables_inp;
  set inter.all_inp (where=( months_21 >= -24 and months_21 <= 23 and state in ('AZ','NY','TX','WI')) 
                      keep = inpatient months_21 female died medicaid private self_pay other_ins  preg_related TOTCHG place
                             inj_by_oth inj_by_self inj_accident alcohol_on_dx alcohol illness state alcohol_any);
   if place = "E8490" then	Home = 1; else Home = 0;
   if place = "E8493" then	Industrial_place = 1; else Industrial_place = 0;
   if place = "E8494" then	Place_recreation_sport = 1; else Place_recreation_sport = 0;
   if place = "E8495" then	Street_highway = 1; else Street_highway = 0;
   if place = "E8496" then	Public_building = 1; else Public_building = 0;
   if place = "E8497" then	Residential_institution = 1; else Residential_institution = 0;
   if place in ("E8498","E8499","E8491","E8492") then Other_place  = 1; else Other_place = 0;
   if place = "" then place_not_coded = 1; else place_not_coded = 0;
   injury_any = inj_accident + inj_by_oth + inj_by_self;
run;

proc freq data = for_tables_inp;
   tables injury_any;
run;

proc means data = for_tables_inp min max mean median p1 p5 p10 p90 p95 p99;
   var TOTCHG;
   class state;
run;

%macro mean_w(source,o_file,a_where);
proc means data = for_tables_&source. noprint;
   var medicaid private self_pay other_ins TOTCHG inpatient female Home Industrial_place Place_recreation_sport Street_highway Public_building Residential_institution Other_place place_not_coded;
   where months_21 >= -24 and months_21 <= 23 and preg_related = 0 &a_where.;
   output out = junk (where=(_stat_ = 'MEAN'));
run;

proc transpose data=junk out=junk1 (rename=(col1=&source._&o_file.));
run;
data &source._&o_file.;
   set junk1;
   if _name_ not in ("TOTCHG","_FREQ_") then &source._&o_file. = 100*&source._&o_file.;
run;

proc sort data = &source._&o_file. (drop = _label_);;
   by _NAME_;
run;

proc datasets;    delete junk junk1; run;

%mend;
%mean_w(ed,all_np,);
%mean_w(ed,illness,and injury_any = 0 and alcohol_any = 0);
%mean_w(ed,injury_any,and injury_any = 1);
%mean_w(ed,alcohol_any,and alcohol_any = 1);

%mean_w(inp,all_np,);
%mean_w(inp,illness,and injury_any = 0 and alcohol_any = 0);
%mean_w(inp,injury_any,and injury_any = 1);
%mean_w(inp,alcohol_any,and alcohol_any = 1);

data combined (drop = _NAME_);
   format blank label_v $50. ed_all_np ed_illness ed_injury_any ed_alcohol_any inp_all_np inp_illness inp_injury_any inp_alcohol_any 8.2 end_line $3.;
   merge ed_all_np ed_illness ed_injury_any ed_alcohol_any inp_all_np inp_illness inp_injury_any inp_alcohol_any;
   by _NAME_;
   if _NAME_ = "inpatient"  then delete;*These are all either 0 or 1;
  *End of line for tex;
   end_line = "\\";
   if _NAME_ = "_TYPE_" then delete;
   if _NAME_ = "FEMALE" then do; 
      sort = 1;
	  label_v = "Female";
   end;
   if _NAME_ = "inpatient" then do; 
      sort = 2;
	  label_v = "Admitted as inpatient";
   end;
   if _NAME_ = "TOTCHG" then do; 
      sort = 3;
	  label_v = "Total Charges";
   end;

   if _NAME_ = "Private" then do; 
      sort = 4;
	  label_v = "Private Insurance";
   end;
   if _NAME_ = "Medicaid" then do; 
      sort = 5;
	  label_v = "Medicaid";
   end;
   if _NAME_ = "other_ins" then do; 
      sort = 6;
	  label_v = "Other Insurance";
   end;
   if _NAME_ = "Self_pay" then do; 
      sort = 7;
	  label_v = "Self Pay";
   end;

   if _NAME_ = "Home" then do; 
      sort = 8;
	  label_v = "Home";
   end;
   if _NAME_ = "Street_highway" then do; 
      sort = 9;
	  label_v = "Street or Highway";
   end;
   if _NAME_ = "Industrial_place" then do; 
      sort = 10;
	  label_v = "Industrial Place";
   end;
   if _NAME_ = "Place_recreation_sport" then do;  
      sort = 11;
	  label_v = "Recreation or Sport";
   end;
   if _NAME_ = "Public_building" then do; 
      sort = 12;
	  label_v = "Public Building";
   end;
   if _NAME_ = "Residential_institution" then do; 
      sort = 13;
	  label_v = "Residential Institution";
   end;
   if _NAME_ = "Other_place" then do; 
      sort = 14;
	  label_v = "Other Place";
   end;
   if _NAME_ = "place_not_coded" then do; 
      sort = 15;
	  label_v = "Place not Coded";
   end;
   if _NAME_ = "_FREQ_" then do; 
      sort = 16;
	  label_v = "Number of Records";
   end;
run;

proc sort data = combined  out = table1 (drop = sort _TYPE_);
   by sort;
run;
proc export data=table1 replace
   outfile='C:\Research\Alcohol and Morbidity\7. Code Archive\Code for Figures and Tables in Paper\Table 1 Raw.xlsx'
   dbms=xlsx;
run;

*ED rates for bottom line of table table 1;
data junk1;
   set inter.alc_rates;
   injury_all_Cause_r = inj_by_self_all_np_r +	inj_by_oth_all_np_r + inj_accident_all_np_r;
run;
proc means data = junk1;
   var  visit_all_np_r illness_all_np_r  injury_all_Cause_r alcohol_any_all_np_r ;
   where months_21 >= -24 and months_21 <= 23;
run;

*In patient rates for bottom line of table;
data junk2;
   set inter.Alc_rates_inp;
   injury_all_Cause_r = inj_by_self_all_np_r +	inj_by_oth_all_np_r + inj_accident_all_np_r;
run;
proc means data = junk2;
   var  visit_all_np_r illness_all_np_r  injury_all_Cause_r alcohol_any_all_np_r ;
   where months_21 >= -24 and months_21 <= 23;
run;
 
